if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CleanupNonCurrentProtocolSubmissionData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CleanupNonCurrentProtocolSubmissionData]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/***************************************************************************
* Name:			CleanupNonCurrentProtocolSubmissionData
* Purpose:		Using table Stg_ProtocolInsert, update certain columns on Protocol table.
*
***************************************************************************/
CREATE PROCEDURE dbo.CleanupNonCurrentProtocolSubmissionData
AS
BEGIN

	SET NOCOUNT ON


	BEGIN
		update stg_protocolReview set IsProtocolCurrent = 1 
			where nIRBProtocolId in ( select nIRBProtocolId from stg_protocolInsert)
	END
/**
	BEGIN 
		truncate table PendingApplications_FCT
	END
**/
	BEGIN
		delete ProtocolBoardReview where IsAnimalBR = 0 AND protocolSubmissionId in 
		( select ProtocolSubmissionId from stg_protocolReview where IsProtocolCurrent = 0)
	END

	BEGIN
		delete Protocolsubmission 
		where ModuleType = 'H' AND id in( select ProtocolSubmissionId from stg_protocolReview where IsProtocolCurrent = 0)
	END

	-- the > is to check that the table isn't empty. This pair of delete statements
	-- is to handle any submissions brought over from Rex that have since been deleted from Rex.
	-- These MUST be removed or else duplicate submissions will exist in the table
	-- NOTE That the 50,000 is a random number that we know there should be more submissions than
	-- this in the Staging table.
	IF (select count(1) from Stg_ProtocolREview) > 50000
	BEGIN	
		 
		DECLARE @boardReviewIdsForDelete as Table (
			PBRId INT NOT NULL )
				
		insert into @boardReviewIdsForDelete ( PBRId )
		
		select Id from ProtocolBoardReview where IsAnimalBR = 0 AND protocolSubmissionId  
			in (select Id from ProtocolSubmission where ModuleType = 'H' AND rexIRBStatusId not in (select RexIRBSTatusId from Stg_ProtocolREview))

		Delete ProtocolBoardReview where IsAnimalBR = 0 AND id in (select PBRId from @boardReviewIdsForDelete)

	END

	-- same thing as above.
	IF (select count(1) from Stg_ProtocolREview) > 50000
	BEGIN	
		
		DECLARE @submissionIdsForDelete as Table (
			SubId INT NOT NULL )
				
		insert into @submissionIdsForDelete ( SubId )	
		select Id from Protocolsubmission where ModuleType = 'H' AND rexIRBStatusId not in (select RexIRBSTatusId from Stg_ProtocolREview)

		delete Protocolsubmission	
			where ModuleType = 'H' AND id in (select SubId from @submissionIdsForDelete)

	END






	SET NOCOUNT OFF

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO